"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 导入导出、备份恢复工具支持M*兼容性
Case Name   : alter function语句，导出再导入
Create At   : 2023.05.23
Owner       : z@wan005
Description :
    1.创建兼容b库
    2.创建用户
    3.创建兼容M*自定义函数，含参数language sql、[not] deterministic、contains sql、
    no sql、reads sql data、modifies sql data、sql security、modifies sql data、
    sql security、definer
    4.创建自定义函数，存储体含repeat
    5.创建自定义函数，存储体含declare cursor
    6.创建自定义函数，存储体含declare cursor
    7.创建自定义函数，存储体为do语句
    8.创建自定义函数，存储体含iterate label
    9.创建自定义函数，存储体含case when condition
    10.创建自定义函数，存储体含while语句
    11.连接导入库查询
    12.使用gs_dump工具导出表为自定义归档格式
    13.gs_restore导入
    14.连接导入库使用show create function语句查询自定义函数
    15.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
    8.成功
    9.成功
    10.成功
    11.成功
    12.成功
    13.成功
    14.成功
    15.成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class GsDump0062(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_dump_0062"
        self.user_name = "user_dump_0062"
        self.func_name = "func_dump_0062"
        self.tb_name = "tb_dump_0062"
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'dump0062')

    def test_server_tools(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '----step1: 创建兼容b库 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name}_01;" \
                  f"create database {self.db_name} dbcompatibility 'b';" \
                  f"create database {self.db_name}_01 dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_result,
                      '执行失败:' + text)

        text = '--step2: 创建用户 expect: 成功--'
        self.log.info(text)
        sql_cmd = f"drop user if exists {self.user_name};" \
                  f"create user {self.user_name} password " \
                  f"'{macro.PASSWD_INITIAL}';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, sql_result,
                      '执行失败:' + text)

        text = '--step3: 创建兼容M*自定义函数，含参数language sql、' \
               '[not] deterministic、contains sql、no sql、reads sql data、' \
               'modifies sql data、sql security、modifies sql data、' \
               'sql security、definer expect: 成功--'
        self.log.info(text)
        sql_cmd = f'''drop function if exists {self.func_name};
        create definer = {self.user_name} function {self.func_name}() \
        returns void language plpgsql
            BEGIN
             return\;
            END;
        alter function {self.func_name} language sql;
        drop function if exists {self.func_name}_01;
        create definer = {self.user_name} function {self.func_name}_01() \
        returns void
            BEGIN
                    return\;
            END;
        alter function {self.func_name}_01 no sql;
        drop function if exists {self.func_name}_02;
        create definer = {self.user_name} function {self.func_name}_02() \
        returns void
            BEGIN
                    return\;
            END;
        alter function {self.func_name}_02 reads sql data;
        drop function if exists {self.func_name}_03;
        create definer = {self.user_name} function {self.func_name}_03() \
        returns void
            BEGIN
                    return\;
            END;
        alter function {self.func_name}_03 contains sql;
        drop function if exists {self.func_name}_04;
        create definer = {self.user_name} function {self.func_name}_04() \
        returns void
            BEGIN
                    return\;
            END;
        alter function {self.func_name}_04 modifies sql data;
        drop function if exists {self.func_name}_05;
        create definer = {self.user_name} function {self.func_name}_05() \
        returns void sql security definer
            BEGIN
                    return\;
            END;
        alter function {self.func_name}_05 sql security invoker;
        drop function if exists {self.func_name}_06;
        create definer = {self.user_name} function {self.func_name}_06() \
        returns void sql security invoker
            BEGIN
                    return\;
            END;
        alter function {self.func_name}_06 sql security definer;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(7, sql_result.count(
            self.constant.CREATE_FUNCTION_SUCCESS_MSG), '执行失败:' + text)
        self.assertEqual(7, sql_result.count('ALTER FUNCTION'),
                         '执行失败:' + text)

        text = '----step4: 使用gs_dump工具导出表为自定义归档格式 expect: 成功----'
        self.log.info(text)
        gs_dump_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_dump " \
                      f"-p {self.dbuser_node.db_port} " \
                      f"{self.db_name} -F c " \
                      f"-f {self.dump_path}"
        self.log.info(gs_dump_cmd)
        dump_msg = self.dbuser_node.sh(gs_dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(f"dump database {self.db_name} successfully",
                      dump_msg, '执行失败:' + text)

        text = '----step5: gs_restore导入 expect: 成功----'
        self.log.info(text)
        gsql_cmd = f"source {macro.DB_ENV_PATH};" \
                   f"gs_restore " \
                   f"-p {self.dbuser_node.db_port} " \
                   f"-d {self.db_name}_01 " \
                   f"{self.dump_path}"
        self.log.info(gsql_cmd)
        gsql_msg = self.dbuser_node.sh(gsql_cmd).result()
        self.log.info(gsql_msg)
        self.assertIn(self.constant.RESTORE_SUCCESS_MSG, gsql_msg,
                      '执行失败:' + text)

        text = '----step6: 连接导入库使用show create function语句查询自定义函数 ' \
               'expect: 成功----'
        self.log.info(text)
        sql_cmd = f"show function status like '%{self.func_name}%';" \
                  f"show create function {self.func_name};"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('7 rows', sql_result, '执行失败' + text)
        self.assertIn('LANGUAGE plpgsql', sql_result, '执行失败' + text)

    def tearDown(self):
        text = '---step7: 清理环境 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name}_01;" \
                  f"drop user if exists {self.user_name};"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname='postgres')
        self.log.info(sql_result)
        rm_cmd = f"rm -rf {self.dump_path}"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        self.assertEqual(2,
                         sql_result.count(self.constant.DROP_DATABASE_SUCCESS),
                         '执行失败:' + text)
        self.assertIn('DROP ROLE', sql_result, '执行失败:' + text)
        self.assertEqual('', rm_msg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
